package com.example.homework09.sqllite;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;

import androidx.annotation.NonNull;
import androidx.fragment.app.Fragment;

import com.example.homework09.R;

public class SQLite extends Fragment {
    private DbHelper dbHelper;

    public SQLite(){
        super(R.layout.sqlite);
    }

    @Override
    public void onViewCreated(@NonNull View view, @NonNull Bundle savedInstanceState){
        super.onViewCreated(view, savedInstanceState);
        dbHelper = new DbHelper(getContext());
        EditText et_name = getView().findViewById(R.id.et_name);
        EditText et_age = getView().findViewById(R.id.et_age);
        TextView textView = getView().findViewById(R.id.tv_show);
        getView().findViewById(R.id.add_btn).setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                SQLiteDatabase db = dbHelper.getWritableDatabase();
                ContentValues values = new ContentValues();
                values.put("username", et_name.getText().toString());
                values.put("age", et_age.getText().toString());
//                        参数1:表名
//                        参数2:传nuL表示不能插入空的values(没有put任何东西)
//                        参数3:要插人的数据
//                        返回新创建行的id,如果创建行失败,返回-1
                long ret = db.insert("users", null, values);
                String msg = ret == -1? "添加失败!" : "添加成功";
                Toast.makeText(getActivity(), msg,Toast.LENGTH_SHORT).show();
                db.close();
                //textView.setText(showDbDate(null));
            }
        });

        getView().findViewById(R.id.ageless18_btn).setOnClickListener(new View.OnClickListener() {
                    @Override
                    public void onClick(View v) {
                        textView.setText(showDbDate("age < 18"));
                    }
                });

    }
    private String showDbDate(String selection) {
        SQLiteDatabase database = dbHelper.getReadableDatabase();
//        参数1:表名
//        参数2:查询结果有包含哪些列,传null表示所有列
//        参数3:查询条件(WHERE语句后面的条件),传null获取所有行
//        参数4:替换参数3中的问号
//        参数5:分组方式(GROUP BY语句后面的值)
//        参数6: HAVING语句后的条件
//        参数7:排序方式
        Cursor cursor = database.query("users",null,selection,null,null,null,null);
        StringBuilder stringBuilder = new StringBuilder();
        while(cursor.moveToNext()){
            stringBuilder
                    .append("id:")
                    .append(cursor.getLong(cursor.getColumnIndex("id")))
                    .append("    username:")
                    .append(cursor.getString(cursor.getColumnIndex("username")))
                    .append( "   age:")
                    .append(cursor.getLong(cursor.getColumnIndex("age")))
                    .append("\n");
        }
        return stringBuilder.toString();
    }
}